const oracledb = require("oracledb");
const conf = require("./config_oracle");
const cjp = conf.cjporcl;
let errMsg

//将结果转换为对象
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
oracledb.autoCommit = true;//设置自动提交
// const conf = {
//     user:"c##wxw",
//     password:"afC090170+",
//     connectString:"(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl.gzmetro.com)))"
// };

// let device = `select * from c##wxw.device where cust_linenum = :line and rownum <= 3`;

//where带变量的查询
exports.search = async function (_sql,_str) {
    let connection; 
    let sel;
    // _sql = device;
    // _str = "L03";
    try {
        connection = await oracledb.getConnection(cjp);   
       
        //execute第一个参数为select语句，第二个参数为指定了变量的值 ，以数组形式传递    
        sel =  await connection.execute(_sql,_str);
        sel = sel.rows;
        return sel;
    } catch (err) {
        console.error(err);
        return err;
    } finally {
        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error(err);
                return err;
            }
        }
    }
}; 

//where不带参数的查询
exports.search2 = async function (_sql) {
    let connection; 
    let sel;
    // _sql = device;
    // _str = "L03";
    try {
        connection = await oracledb.getConnection(cjp);        
        sel =  await connection.execute(_sql);
        sel = sel.rows;
        return sel;
    } catch (err) {
        console.error(err);
        return err;
    } finally {
        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error(err);
                return err;
            }
        }
    }
};

//单行更新或插入
exports.writeTab = async function (_sql,_str) {
    let connection; 
    let sel;
    
    try {
        connection = await oracledb.getConnection(cjp);        
        sel =  await connection.execute(_sql,_str);
        // sel = sel.rows;
        return sel;
    } catch (err) {
        console.error(err);
        return err;
    } finally {
        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error(err);
                return err;
            }
        }
    }
};

exports.writeTab2 = async function (_sql) {
    let connection; 
    let sel;
    
    try {
        connection = await oracledb.getConnection(cjp);        
        sel =  await connection.execute(_sql);
        // sel = sel.rows;
        return sel;
    } catch (err) {
        console.error(err);
        return err;
    } finally {
        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error(err);
                return err;
            }
        }
    }
};

//批量更新/插入
exports.writeManyTab = async function (_sql,_binds,_opt) {
    let connection; 
    let sel;    
    try {
        connection = await oracledb.getConnection(cjp);        
        sel =  await connection.executeMany(_sql,_binds,_opt);
        return sel;
    } catch (err) {
        console.error(err);
        return err;
    } finally {
        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error(err);
                return err;
            }
        }
    }
};

exports.writeManyTab2 = async function (_sql,_opt) {
    let connection; 
    let sel;    
    try {
        connection = await oracledb.getConnection(cjp);        
        sel =  await connection.executeMany(_sql,_opt);
        return sel;
    } catch (err) {
        console.error(err);
        return err;
    } finally {
        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error(err);
                return err;
            }
        }
    }
};
